CS 432
This assignment has to be handed in in your handin
directory on goose in the CSUGLAB. No other ways of handing in the assignment
are permitted. Detailed instructions on handing in your assignment are at the
bottom of this page.
A list of frequently asked questions to assignment 2.
You have to have an account in the undergraduate lab to submit your assignment. Submissions on disks or per email are not permitted. No late submissions are allowed. If you suddenly realize the day before the assignment is due that you do not have a handin directory with your login name, there is nothing we can do. You have to check early (by Monday, September 18) whether there is a handin directory with your login name. Send email to johannes@cs.cornell.edu if you do not have a directory.
This assignment will use a database containing data about a university. The
relations are in a
Microsoft ACCESS 2000 database in the following location:
\\goose\courses\cs432-fall01\A2\cs432.mdb
The schema of the database is provided below (keys are in bold, field types are
omitted):
· student(sid,
sname, sex, age, year, gpa)
· dept(dname,
numphds)
· prof(pname,
dname)
· course(cno,
cname, dname)
· major(dname,
sid)
· section(dname,
cno, sectno, pname)
· enroll(sid,
grade, dname, cno, sectno)
Before you start writing SQL, it is a good idea
to take a look at the database and familiarize yourself with its contents.
Write SQL queries that answer the questions below (one query per question)
and run them on the Microsoft
ACCESS Database System using its SQL interpreter. The query answers
must not contain duplicates, but
you should use the SQL keyword distinct only when
necessary. You should copy the
database from the class directory into your personal directory before you start
writing queries.
The SQL interpreter in ACCESS is not quite the
same as the one described in the textbook. If the query you write is not
accepted by ACCESS (usually it gives you some strange errors), try different
ways until you get one that works with ACCESS. For this assignment, creation of
temporary tables is not allowed, i.e., for each question you have to write
exactly one SQL statement.
Write the following SQL queries:
1. Print
the names of professors who work in departments that have fewer than 50 PhD
students.
2. Print
the name(s) of student(s) with the lowest gpa.
3. For
each Computer Sciences class (section), print the cno, sectno, and the average
gpa of the students enrolled in the class.
4. Print
the course names, course numbers and section numbers of all classes with less
than six students enrolled in them.
5. Print
the name(s) and sid(s) of the student(s) enrolled in the most classes.
6. Print
the names of departments that have one or more majors who are under 18 years
old.
7. Print
the names and majors of students who have a major and are taking one of the
College Geometry courses. (Hint: You'll need to use the "like"
predicate and the string matching character in your query.)
8. For
those departments that have no majors taking a College Geometry course, print
the department name and the number of PhD students in the department.
9. Print
the names of students who are taking both a Computer Sciences course and a
Mathematics course.
10. Print
the age difference between the oldest and youngest Computer Sciences major(s).
11. For
each department that has one or more majors with a GPA under 1.0, print the
name of the department and the average GPA of its majors.
12. Print
the ids, names, and GPAs of the students who are currently taking all of the
Civil Engineering courses.
This is an individual assignment – no group
submissions are allowed. Hand in an ACCESS database that contains the answers to
the twelve questions. The database should contain twelve queries, named as
follows:
Query1
Query2
…
Query12
You have to hand your database electronically
into your handin directory by the due date. The name of your
database should be your undergraduate lab login id. For example, if your
undergraduate login identifier is xyz, your database should be named xyz.mdb.
We created a personal handin directory with your undergraduate lab login id in
the following folder:
\\goose\courses\cs432-fall01\HandinA2\xyz
where xyz is your undergraduate lab login
identifier. Check as soon as possible whether a directory with your id exists
and whether you can access the directory, and send email to the instructor (johannes@cs.cornell.edu)
if you do not have a directory or you do not have the correct permissions in
your directory. You can modify and remove files in your handin directory
before the deadline as often as you want.